package com.meizi.codegenerate.utils;

import com.alibaba.druid.pool.DruidDataSource;
import com.meizi.codegenerate.entity.ColumnsEntity;
import com.meizi.codegenerate.entity.TablesEntity;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @author GG Bond
 * @date 2022/12/28 - 16:02 - 星期三
 * @project lambda
 * @package com.meizi.codegenerate.utils
 * @description Druid连接池的工具类
 */
public class JDBCUtils {

    /**
     * 初始化Druid连接池
     * @return
     */
    public static DruidDataSource gainDruid () {

        DruidDataSource dataSource = new DruidDataSource();
        try {
            //基本属性
            dataSource.setDriverClassName("com.mysql.jdbc.Driver");
            dataSource.setUrl("jdbc:mysql://192.168.0.117:3306/iot_smart_site?allowMultiQueries=true&useUnicode=true&characterEncoding=utf8&autoReconnect=true&serverTimezone=GMT%2B8&useSSL=false");
            dataSource.setUsername("root");
            dataSource.setPassword("asdqwe123@");

            /*dataSource.setDriverClassName("com.mysql.jdbc.Driver");
            dataSource.setUrl("jdbc:mysql://127.0.0.1:3306/shopping?allowMultiQueries=true&useUnicode=true&characterEncoding=utf8&autoReconnect=true&serverTimezone=GMT%2B8&useSSL=false");
            dataSource.setUsername("root");
            dataSource.setPassword("123456");*/

            //初始化时建立物理连接的个数
            dataSource.setInitialSize(5);
            //最大连接池数量
            dataSource.setMaxActive(15);
            //最小连接池数量
            dataSource.setMinIdle(10);
            //获取连接时最大等待时间，单位毫秒
            dataSource.setMaxWait(60000);

        } catch (Exception e) {
            e.printStackTrace();
        }
        return dataSource;
    }


    /**
     * 获取连接
     */
    public static Connection getConnection() throws SQLException {
        return gainDruid().getConnection();
    }

    public static void close(ResultSet rs , Statement stmt, Connection conn){

        if(rs != null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }


        if(stmt != null){
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if(conn != null){
            try {
                conn.close();//归还连接
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 查询返回数据表字段类型
     * @param tableName 数据表名
     * @return
     */
    public static List<ColumnsEntity> queryColumns (String tableName){

        Connection conn = null;
        PreparedStatement pstmt = null;
        List<ColumnsEntity> columnsEntityS = null;
        try {
            //1.获取连接
            conn = JDBCUtils.getConnection();
            //2.定义sql
            String sql = "show columns from " + tableName;
            //3.获取pstmt对象
            pstmt = conn.prepareStatement(sql);
            ResultSet resultSet = pstmt.executeQuery(sql);
            columnsEntityS = new ArrayList<>();
            while (resultSet.next()){
                ColumnsEntity columnsEntity = new ColumnsEntity();
                columnsEntity.setAttribute(resultSet.getString("Field"),
                        resultSet.getString("Type"),
                        resultSet.getString("Null"),
                        resultSet.getString("Key"),
                        resultSet.getString("Default"),
                        resultSet.getString("Extra"));
                columnsEntityS.add(columnsEntity);
            }
        }catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(null, pstmt, conn);
        }

        return columnsEntityS;
    }

    /**
     * 获取表备注
     * @param tableName
     * @return
     */
    public static TablesEntity queryTablesComment (String tableName){

        Connection conn = null;
        PreparedStatement pstmt = null;
        TablesEntity tablesEntity = null;
        try {
            //1.获取连接
            conn = JDBCUtils.getConnection();
            //2.定义sql
            String sql = "SELECT " +
                    "TABLE_NAME, TABLE_COMMENT " +
                    "FROM " +
                    "information_schema.TABLES " +
                    "WHERE " +
                    "TABLE_Name = " + "'" + tableName + "'";
            //3.获取pstmt对象
            pstmt = conn.prepareStatement(sql);
            ResultSet resultSet = pstmt.executeQuery(sql);
            tablesEntity = new TablesEntity();
            while (resultSet.next()){
                tablesEntity.setAttribute(resultSet.getString("TABLE_NAME"),
                        resultSet.getString("TABLE_COMMENT"));
            }
        }catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(null, pstmt, conn);
        }

        return tablesEntity;
    }


    /**
     * 获取数据表字段备注信息
     * @param tableName
     * @return
     */
    public static List<ColumnsEntity> queryColumnsComment(String tableName) {

        Connection conn = null;
        PreparedStatement pstmt = null;
        List<ColumnsEntity> columnsCommentS = null;
        try {
            //1.获取连接
            conn = JDBCUtils.getConnection();
            //2.定义sql
            String sql = "SELECT " +
                    "COLUMN_NAME, " +
                    "COLUMN_COMMENT " +
                    "FROM " +
                    "information_schema.COLUMNS " +
                    "WHERE " +
                    "TABLE_SCHEMA = DATABASE()" + //- 限定当前数据库
                    "AND TABLE_NAME = " + "'" + tableName + "'";
            //3.获取pstmt对象
            pstmt = conn.prepareStatement(sql);
            ResultSet resultSet = pstmt.executeQuery(sql);
            columnsCommentS = new ArrayList<>();
            while (resultSet.next()){
                ColumnsEntity columnsEntity = new ColumnsEntity();
                columnsEntity.setFieldComment(resultSet.getString("COLUMN_NAME"),
                        resultSet.getString("COLUMN_COMMENT"));
                columnsCommentS.add(columnsEntity);
            }
        }catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(null, pstmt, conn);
        }

        return columnsCommentS;
    }
}
